﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Globalization;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.IO;
using PDFPlatform;
using System.Transactions;
using System.Net.Mail;

/// <summary>
/// clsDBUtility 的摘要描述
/// </summary>

public partial class clsDBUtility
{
  private SqlConnection conn;

    public void WriteErrorLog(string ProgamName, string ErrMsg, string FunctionName, string byWho, string ErrStatus)
    {

 
      
        SqlParameter[] sqlParams = new SqlParameter[5];

        sqlParams[0] = new SqlParameter("@ProgamName", SqlDbType.NVarChar, ProgamName.Length);
        sqlParams[0].Value = ProgamName;
        sqlParams[1] = new SqlParameter("@ErrMsg", SqlDbType.NVarChar, ErrMsg.Length);
        sqlParams[1].Value = ErrMsg;
        sqlParams[2] = new SqlParameter("@FunctionName", SqlDbType.NVarChar, FunctionName.Length);
        sqlParams[2].Value = FunctionName;
        sqlParams[3] = new SqlParameter("@byWho", SqlDbType.NVarChar, byWho.Length);
        sqlParams[3].Value = byWho;
        sqlParams[4] = new SqlParameter("@ErrStatus", SqlDbType.NVarChar, ErrStatus.Length);
        sqlParams[4].Value = ErrStatus;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" insert into  ACMSErrorLog (Program ,ErrMsg ,FunctionName ,ByWho ,ErrStatus ) values(@ProgamName,@ErrMsg,@FunctionName,@byWho,@ErrStatus) ");


        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);


    }
   
    /// <summary>
    /// 取得資料庫的連線設定
    /// </summary>
    /// <param name="strConnSring"></param>
    public clsDBUtility(string strConnSring)
    {
        conn = new SqlConnection(ConfigurationManager.AppSettings[strConnSring].Trim());
    }

    /// <summary>
    /// 取得人事系統的員工檔資料
    /// </summary>
    /// <returns></returns>
    public DataTable SelectEmployeeSource()
    {
        try
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * ");
            sb.AppendLine("FROM V_ACSM_USER WHERE ISNULL(ID,'')<>'' and ISNULL(NATIVE_NAME,'')<>'' ");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

            if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
            {
                return DS.Tables[0];
            }
            else
            {
                LogMsg.Log("[SelectEmployeeData]-Rows Count = 0 ", 1, false);
                
                return null;
            }
        }
        catch (Exception ex)
        {
            LogMsg.Log(string.Format("[SelectEmployeeData]-Exception:{0}", ex.Message), 1, false);
            WriteErrorLog("BatchUpdae", ex.Message, "SelectEmployeeSource", "SyS", "0");
            return null;
        }

    }

    public void InsertEmployeeData(DataTable dt)
    {
        string nativename = "";
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            string birthday;
            using (SqlTransaction trans = conn.BeginTransaction())
            {

                StringBuilder sb = new StringBuilder();

                sb.AppendLine("truncate Table V_ACSM_USER_TMP; ");

                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString(), null);

                foreach (DataRow dr in dt.Rows)
                {
                    nativename = dr["NATIVE_NAME"].ToString();
                    try
                    {
                        birthday = DateTime.Parse(dr["BIRTHDAY"].ToString()).ToString("yyyy/MM");
                    }
                    catch
                    {
                        birthday = "1969/03";
                    }
                    sb.Length = 0;
                    sb.AppendLine("INSERT V_ACSM_USER_TMP( ID,NATIVE_NAME,ENGLISH_NAME,WORK_ID,OFFICE_MAIL,DEPT_ID,C_DEPT_NAME,C_DEPT_ABBR");
                    sb.AppendLine(",OFFICE_PHONE,EXPERIENCE_START_DATE,BIRTHDAY,SEX,JOB_CNAME,STATUS,WORK_END_DATE,COMPANY_CODE,C_NAME,JOB_GRADE_GROUP,createat) ");
                    sb.AppendLine(string.Format("SELECT '{0}',N'{1}',N'{2}','{3}','{4}','{5}',N'{6}',N'{7}','{8}','{9}','{10}','{11}',N'{12}','{13}','{14}','{15}',N'{16}','{17}',getdate() "
                        , dr["id"]
                        , dr["NATIVE_NAME"]
                        , dr["ENGLISH_NAME"]
                        , dr["WORK_ID"]
                        , dr["OFFICE_MAIL"]
                        , dr["DEPT_ID"]
                        , dr["C_DEPT_NAME"]
                        , dr["C_DEPT_ABBR"]
                        , dr["OFFICE_PHONE"]
                        , (dr["EXPERIENCE_START_DATE"] == DBNull.Value ? null : Convert.ToDateTime(dr["EXPERIENCE_START_DATE"]).ToString("yyyy/MM/dd"))
                        , birthday
                        , dr["SEX"]
                        , dr["JOB_CNAME"]
                        , dr["STATUS"]
                        , (dr["WORK_END_DATE"] == DBNull.Value ? null : Convert.ToDateTime(dr["WORK_END_DATE"]).ToString("yyyy/MM/dd"))
                        , dr["COMPANY_CODE"], dr["C_NAME"], dr["JOB_GRADE_GROUP"]));

                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString(), null);
                }

                sb.Length = 0;
                sb.AppendLine("EXEC sp_rename 'V_ACSM_USER','V_ACSM_USER_TMP2'; ");
                sb.AppendLine("EXEC sp_rename 'V_ACSM_USER_TMP','V_ACSM_USER'; ");
                sb.AppendLine("EXEC sp_rename 'V_ACSM_USER_TMP2','V_ACSM_USER_TMP'; ");

                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString(), null);

                trans.Commit();

                LogMsg.Log("[InsertEmployeeData]-OK ", 1, false);
                WriteErrorLog("BatchUupdate", "InsertEmployeeData.OK  ","InsertEmployeeData ","Sys","1");


            }

        }
        catch (Exception ex)
        {

            LogMsg.Log(string.Format("[InsertEmployeeData]-Exception:{0}", nativename + ":" + ex.Message), 1, false);
            WriteErrorLog("BatchUupdate", ex.Message , "InsertEmployeeData ", "Sys", "1");


           // SendMail();
        }
        finally
        {
            conn.Close();
        }
    }
    public void SendMail()
    {
        try
        {
            MailMessage mail;
            string[] smtpto = System.Configuration.ConfigurationManager.AppSettings["SMTPTo"].Split(',');
            MailAddress mailFrom = new MailAddress(System.Configuration.ConfigurationManager.AppSettings["SMTPFrom"], "報名系統通知");
            SmtpClient smtp = new SmtpClient(System.Configuration.ConfigurationManager.AppSettings["SMTPServer"]);

            mail = new MailMessage();

            foreach (string st1 in smtpto)
            {
                mail.To.Add(st1);
            }

            mail.Subject = "報名系統自動更新錯誤通知";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;
            mail.Body = "報名系統自動更新錯誤通知";
            mail.Attachments.Add(new Attachment(AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\') + "\\Log\\Log" + DateTime.Today.ToString("yyyy/MM/dd") + ".txt"));



        
           
            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                //clsMyObj.ShowMessage(ex.Message );

            }

        }
        catch (Exception ex1)
        {

        }


    }

    /// <summary>
    /// 1.修改報名資料中的離職/留停狀態
    /// 2.刪除超過N年的報名及活動相關資料    
    /// </summary>
    public void UpdateStatus()
    {
        try
        {
            StringBuilder sb = new StringBuilder();

            //離職
            sb.AppendLine("Update ActivityRegist ");
            sb.AppendLine("set check_status=-2 ");
            sb.AppendLine("WHERE emp_id in (SELECT ID FROM V_ACSM_USER2 WHERE status='2'); ");

            //離職
            sb.AppendLine("Update ActivityTeamMember ");
            sb.AppendLine("set check_status=-2 ");
            sb.AppendLine("WHERE emp_id in (SELECT ID FROM V_ACSM_USER2 WHERE status='2'); ");

            //留職停薪
            sb.AppendLine("Update ActivityRegist ");
            sb.AppendLine("set check_status=-3 ");
            sb.AppendLine("WHERE emp_id in (SELECT ID FROM V_ACSM_USER2 WHERE status='3'); ");

            //留職停薪
            sb.AppendLine("Update ActivityTeamMember ");
            sb.AppendLine("set check_status=-3 ");
            sb.AppendLine("WHERE emp_id in (SELECT ID FROM V_ACSM_USER2 WHERE status='3'); ");

            ////--先將Activity 搬到HisActivity 中 以下為 andy add =========================
            //sb.AppendLine("Insert into HisActivity (sn,id,activity_type , activity_info ,	org_id ,activity_name ,people_type, activity_startdate,activity_enddate,");
            //sb.AppendLine("	limit_count,	limit2_count,	team_member_min,team_member_max,regist_startdate,regist_deadline,cancelregist_deadline,	is_showfile,is_showprogress,");
            //sb.AppendLine("	is_showperson_fix1,	is_showperson_fix2,	personextcount_min,	personextcount_max,is_showidno,is_showremark,remark_name,is_showteam_fix1,is_showteam_fix2,");
            //sb.AppendLine("	teamextcount_min,teamextcount_max,is_grouplimit,notice,	active)");
            //sb.AppendLine("select sn,id,activity_type , activity_info ,	org_id ,activity_name ,people_type, activity_startdate,activity_enddate,");
            //sb.AppendLine("	limit_count,	limit2_count,	team_member_min,team_member_max,regist_startdate,regist_deadline,cancelregist_deadline,	is_showfile,is_showprogress,");
            //sb.AppendLine("	is_showperson_fix1,	is_showperson_fix2,	personextcount_min,	personextcount_max,is_showidno,is_showremark,remark_name,is_showteam_fix1,is_showteam_fix2,");
            //sb.AppendLine("	teamextcount_min,teamextcount_max,is_grouplimit,notice,	active ");
            //sb.AppendLine("	From Activity  	where dateadd (year,3,activity_enddate)<GETDATE()");
            ////--將ActivityGroupLimit搬到HisActivityGroupLimit
            //sb.AppendLine("insert into HisActivityGroupLimit( id , activity_id,emp_id)");
            //sb.AppendLine("select id , activity_id,emp_id  from ActivityGroupLimit");
            //sb.AppendLine("where activity_id in (select id from HisActivity)");
            ////--將ActivityRegist 搬到 HisActivityRegist
            //sb.AppendLine("insert into HisActivityRegist(id ,activity_id,emp_id,regist_by,idno_type,idno,team_name,ext_people,createat,check_status)");
            //sb.AppendLine("select id ,activity_id,emp_id,regist_by,idno_type,idno,team_name,ext_people,createat,check_status");
            //sb.AppendLine("from ActivityRegist  where activity_id in (select id from HisActivity)");
            ////--將ActivityTeamMember 搬到HisActivityTeamMember
            //sb.AppendLine("insert into HisActivityTeamMember (activity_id,emp_id,boss_id,idno_type,idno,remark,check_status)");
            //sb.AppendLine("select activity_id,emp_id,boss_id,idno_type,idno,remark,check_status from ActivityTeamMember");
            //sb.AppendLine(" where activity_id in (select id from HisActivity)");
            ////--將CustomField 搬到HisCustomField
            //sb.AppendLine("insert into HisCustomField(activity_id,field_id,field_name,field_control)");
            //sb.AppendLine("select activity_id,field_id,field_name,field_control ");
            //sb.AppendLine("from CustomField ");
            //sb.AppendLine("where activity_id in (select id from HisActivity)");
            ////--將CustomFieldItem 搬到HisCustomFieldItem
            //sb.AppendLine("insert into HisCustomFieldItem(field_id,field_item_id,field_item_name ,field_item_text)");
            //sb.AppendLine("select field_id,field_item_id,field_item_name ,field_item_text");
            //sb.AppendLine("from CustomFieldItem ");
            //sb.AppendLine("where field_id in (select field_id from HisCustomField)");
            ////--將[CustomFieldValue] 搬到[HisCustomFieldValue]
            //sb.AppendLine("insert into HisCustomFieldValue(id,	emp_id,field_id,field_value)");
            //sb.AppendLine("select id,	emp_id,field_id,field_value");
            //sb.AppendLine("from CustomFieldValue ");
            //sb.AppendLine("where field_id in (select field_id from HisCustomField)");
            ////--將Activity中已達3年的資料刪除
            //sb.AppendLine("delete from Activity where id in (select id from HisActivity)");
            ////--將ActivityGroupLimit中已達3年的資料刪除
            //sb.AppendLine("delete from ActivityGroupLimit where activity_id in (select activity_id from HisActivityGroupLimit )	");
            ////--將ActivityRegist中已達3年的資料刪除
            //sb.AppendLine("delete from ActivityRegist where activity_id in (select activity_id from HisActivityRegist )");
            ////--將ActivityTeamMember中已達3年的資料刪除
            //sb.AppendLine("delete from ActivityTeamMember where activity_id in (select activity_id from HisActivityTeamMember )");
            ////delete from CustomField where activity_id in (select activity_id from HisActivityTeamMember )
            ////--將CustomFieldItem中已達3年的資料刪除
            //sb.AppendLine("delete from CustomFieldItem where field_id in (select field_id from HisCustomFieldItem )");
            ////--將CustomFieldItem中已達3年的資料刪除
            //sb.AppendLine("delete from CustomFieldValue where field_id in (select field_id from HisCustomFieldValue )");
            //=============================================================================================
            //2011/1/17  andy因為華碩的DB,有容量管制所以無法將3年以上的資料加以保留,所以改為只要超過3年則需要加以刪除
            string ReservedTime = System.Configuration.ConfigurationManager.AppSettings["ReservedTime"];//保留時間(N年)
            if (ReservedTime == null)//沒有值代入3年
            {
                ReservedTime = "3";
            }
            sb.AppendLine(" delete from Activity where dateadd (year," + ReservedTime + ",activity_enddate)<GETDATE()");
            //--將ActivityGroupLimit中已達3年的資料刪除
            sb.AppendLine("delete from ActivityGroupLimit where not activity_id in (select id from Activity )	");
            //--將ActivityRegist中已達3年的資料刪除
            sb.AppendLine("delete from ActivityRegist where not activity_id in (select id from Activity )	");
            //--將ActivityTeamMember中已達3年的資料刪除
            sb.AppendLine("delete from ActivityTeamMember where not activity_id in (select id from Activity )	");
            sb.AppendLine("delete from CustomField where not activity_id in (select id from Activity )	");
            //--將CustomFieldItem中已達3年的資料刪除
            sb.AppendLine("delete from CustomFieldItem where not  field_id in (select field_id from CustomField )");
            //--將CustomFieldItem中已達3年的資料刪除
            sb.AppendLine("delete from CustomFieldValue where not field_id in (select field_id from CustomField )");

            //--將取消名中已達3年的資料刪除
            sb.AppendLine("delete from ActivityRegistCancel where not activity_id in (select id from Activity ) ");
           // sb.AppendLine("Insert into  V_ACSM_USER (ID, NATIVE_NAME) values ('SYS','SYS') ");
            //============================================================================================'

            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), null);

            LogMsg.Log("[UpdateStatus]-OK ", 1, false);
        }
        catch (Exception ex)
        {
            LogMsg.Log(string.Format("[UpdateStatus]-Exception:{0}", ex.Message), 1, false);
            WriteErrorLog("BatchUpdate", ex.Message, "UpdateStatus", "Sys", "0");
        }

    }

    public void SendCancelListMail()
    {
        StringBuilder sb = new StringBuilder();


        sb.AppendLine("insert into ActivityRegistCancel");
        sb.AppendLine("select A.activity_id,A.emp_id,A.boss_id,B.team_name ,B.regist_by ,B.createat ,'SYS' ,Getdate() from   ActivityTeamMember A");
        sb.AppendLine("left join ActivityRegist  B on A.activity_id =B.activity_id ");
        sb.AppendLine("where B.check_status <0");
        sb.AppendLine("delete from ActivityTeamMember  where check_status <0");


        sb.AppendLine("insert into ActivityRegistCancel");
        sb.AppendLine("select activity_id,emp_id,'',''  ,regist_by,createat ,'SYS' ,Getdate() from   ActivityRegist ");
        sb.AppendLine("where check_status <0");
        sb.AppendLine("delete from ActivityRegist where check_status <0");


        sb.AppendLine("insert into ActivityRegistCancel");


        sb.AppendLine("select A.activity_id,A.emp_id,A.boss_id,C.team_name ,C.regist_by ,C.createat ,'SYS' ,Getdate() from ActivityTeamMember A");
        sb.AppendLine("inner join ");
        sb.AppendLine("(select A.*,B.team_member_min  from ");
        sb.AppendLine("(select activity_id,boss_id,COUNT(*) as recCount from ActivityTeamMember");
        sb.AppendLine("where check_status >=0 and  activity_id in");
        sb.AppendLine("(select A.id from Activity A");
        sb.AppendLine("where A.activity_startdate >GETDATE() and A.activity_type='2')");
        sb.AppendLine("group by activity_id ,boss_id )A");
        sb.AppendLine("left join Activity  B on A.activity_id=B.id ");
        sb.AppendLine("where  B.team_member_min >A.recCount) AA on A.activity_id=AA.activity_id and A.boss_id =AA.boss_id ");
        sb.AppendLine("left join V_ACSM_USER2 B on A.emp_id =B.ID ");
        sb.AppendLine("left join ActivityRegist C on A.activity_id =C.activity_id ");


        sb.AppendLine("select A.activity_id ,A.emp_id ,A.boss_id ,B.OFFICE_MAIL ,AA.activity_name,AA.recCount,AA.team_member_min ");
        sb.AppendLine("into #AA");
        sb.AppendLine("from  ActivityTeamMember A");
        sb.AppendLine("inner join ");
        sb.AppendLine("(select A.*,B.team_member_min ,B.activity_name   from ");
        sb.AppendLine("(select activity_id,boss_id,COUNT(*) as recCount from ActivityTeamMember");
        sb.AppendLine("where check_status >=0 and  activity_id in");
        sb.AppendLine("(select A.id from Activity A");
        sb.AppendLine("where A.activity_startdate >GETDATE() and A.activity_type='2')");
        sb.AppendLine("group by activity_id ,boss_id )A");
        sb.AppendLine("left join Activity  B on A.activity_id=B.id ");
        sb.AppendLine("where  B.team_member_min >A.recCount) AA on A.activity_id=AA.activity_id and A.boss_id =AA.boss_id ");
        sb.AppendLine("left join V_ACSM_USER2 B on A.emp_id =B.ID ");
        sb.AppendLine("left join ActivityRegist C on A.activity_id =C.activity_id ");
        sb.AppendLine("select * from #AA");

        sb.AppendLine("delete  from ActivityRegist  where convert (varchar(100),activity_id)+emp_id  in (select convert (varchar(100),activity_id) +emp_id   from #AA)");
        sb.AppendLine("delete  from ActivityTeamMember  where convert (varchar(100),activity_id)+emp_id  in (select convert (varchar(100),activity_id) +emp_id   from #AA)");

        sb.AppendLine("drop table #AA");

        DataTable dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null).Tables[0];


        MailMessage mail;
        string mailtype = System.Configuration.ConfigurationManager.AppSettings["MailType"].ToLower();
        string[] smtpto = System.Configuration.ConfigurationManager.AppSettings["SMTPTo"].Split(',');
        string webPath = System.Configuration.ConfigurationManager.AppSettings["webPath"].ToLower();
        MailAddress mailFrom = new MailAddress(System.Configuration.ConfigurationManager.AppSettings["SMTPFrom"], "報名系統通知");
        SmtpClient smtp = new SmtpClient(System.Configuration.ConfigurationManager.AppSettings["SMTPServer"]);

        foreach (DataRow dr in dt.Rows)
        {


            mail = new MailMessage();
            if ((string.Compare(mailtype, "online") != 0))
            {
                foreach (string st1 in smtpto)
                {
                    mail.To.Add(st1);
                }
            }
            else
            {
                mail.To.Add(dr["OFFICE_MAIL"].ToString());
            }

            mail.Subject = "活動名稱：" + dr["activity_name"] + "，活動取消通知";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;
            mail.Body = "<table border='1'  ><tr><td style='background:#548DD4;Color:White' align='center' > 活動取消通知</td></tr><tr><td style ='padding-left:50px;padding-right:50px'>"
                //<b>"
                + "<br />親愛的同仁您好：<br />提醒您！您報名之　<font color='Blue'>" + dr["activity_name"].ToString() + "</font>　活動<br/> 因有隊友離職或留停導致人數不足，系統已取消該隊報名資格<br/>目前該隊報名人數:"
                + dr["recCount"].ToString() + "，而該活動的下限人數為:" + dr["team_member_min"].ToString() + "<br/>取消時間：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "<br /><br/>"

                 + "</td></tr>"
                   + "<tr><td align='center' ></font> <a href='" + webPath + "Default.aspx" + "'>" + "報名系統聯結" + " </a></td> </tr>"

                   + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";


            

            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                //clsMyObj.ShowMessage(ex.Message );
                WriteErrorLog("BatchUpdate", ex.Message, "SendOneDayMail", "Sys", "0");


            }



        }


    }




    public void SendCanJoinMail()
    {
        StringBuilder sb = new StringBuilder();


      


        sb.AppendLine("SELECT Activity_id,emp_id,   ");
        sb.AppendLine("ROW_NUMBER() OVER(PARTITION BY Activity_id ORDER BY createat) AS 'SeqNo'  ");
        sb.AppendLine("into #AA  ");
        sb.AppendLine("FROM ActivityRegist  where activity_id in (select id from Activity where convert(date,activity_startdate) > CONVERT(date,getdate())) and check_status >=0  ");
        sb.AppendLine("select A.emp_id ,C.activity_name ,C.activity_startdate ,C.activity_enddate ,E.OFFICE_MAIL ,B.SeqNo  ");
        sb.AppendLine("from  ActivityRegist A   ");
        sb.AppendLine("inner join #AA B on A.activity_id =B.activity_id and A.emp_id =B.emp_id   ");
        sb.AppendLine("inner join Activity C on a.activity_id =c.id   ");
        sb.AppendLine("inner join V_ACSM_USER2 E on A.emp_id =E.ID   ");
        sb.AppendLine("where A.SeqNo>C.limit_count and B.SeqNo<=C.limit_count  and c.activity_type ='1'  ");
        sb.AppendLine("select D.emp_id ,C.activity_name ,C.activity_startdate ,C.activity_enddate ,E.OFFICE_MAIL  ,B.SeqNo ");
        sb.AppendLine("from  ActivityRegist A   ");
        sb.AppendLine("inner join #AA B on A.activity_id =B.activity_id and A.emp_id =B.emp_id   ");
        sb.AppendLine("inner join Activity C on a.activity_id =c.id   ");
        sb.AppendLine("inner  join ActivityTeamMember D on a.activity_id =d.activity_id and A.emp_id = D.boss_id  ");
        sb.AppendLine("inner join V_ACSM_USER2 E on D.emp_id =E.ID   ");
        sb.AppendLine("where A.SeqNo>C.limit_count and B.SeqNo<=C.limit_count  and c.activity_type ='2'  ");
        sb.AppendLine("update A set A.SeqNo=B.SeqNo from  ActivityRegist A inner join #AA B on A.activity_id =B.activity_id and A.emp_id =B.emp_id   ");
        sb.AppendLine("drop table #AA   ");
        DataSet ds =SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);
        DataTable dt = ds.Tables[0];
      
        string emailAddr = "";
        string ccAddr = "";
        MailMessage mail;
        string mailtype = System.Configuration.ConfigurationManager.AppSettings["MailType"].ToLower();
        string[] smtpto = System.Configuration.ConfigurationManager.AppSettings["SMTPTo"].Split(',');
        string webPath = System.Configuration.ConfigurationManager.AppSettings["webPath"].ToLower();
        MailAddress mailFrom = new MailAddress(System.Configuration.ConfigurationManager.AppSettings["SMTPFrom"], "報名系統通知");
        SmtpClient smtp = new SmtpClient(System.Configuration.ConfigurationManager.AppSettings["SMTPServer"]);
        //寄個人活動
        foreach (DataRow dr in dt.Rows)
        {


            mail = new MailMessage();
            if ((string.Compare(mailtype, "online") != 0))
            {
                foreach (string st1 in smtpto)
                {
                    mail.To.Add(st1);
                }
            }
            else
            {
                mail.To.Add(dr["OFFICE_MAIL"].ToString());
            }

            mail.Subject = "活動名稱：" + dr["activity_name"] + "，活動通知";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;
            mail.Body = "<table border='1'  ><tr><td style='background:#548DD4;Color:White' align='center' > 活動通知</td></tr><tr><td style ='padding-left:50px;padding-right:50px'>"
                //<b>"
                + "<br />親愛的同仁您好：<br />提醒您！您報名之　<font color='Blue'>" + dr["activity_name"].ToString() + "</font>　活動<br/> 因有人臨時取消報名，你已由原備取轉為正取<br/>"
                + "<font color='Blue'>目前編號：</font>正取" + dr["SeqNo"].ToString()
                + "</td></tr>"
                + "<tr><td align='center' ></font> <a href='" + webPath + "Default.aspx" + "'>" + "報名系統聯結" + " </a></td> </tr>"
                + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";


         
            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                //clsMyObj.ShowMessage(ex.Message );
                WriteErrorLog("BatchUpdate", ex.Message, "SendCanJoinMail", "Sys", "0");


            }



        }
        //寄團隊活動
        dt = ds.Tables[1];
        foreach (DataRow dr in dt.Rows)
        {


            mail = new MailMessage();
            if ((string.Compare(mailtype, "online") != 0))
            {
                foreach (string st1 in smtpto)
                {
                    mail.To.Add(st1);
                }
            }
            else
            {
                mail.To.Add(dr["OFFICE_MAIL"].ToString());
            }

            mail.Subject = "活動名稱：" + dr["activity_name"] + "，活動通知";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;
            mail.Body = "<table border='1'  ><tr><td style='background:#548DD4;Color:White' align='center' > 活動通知</td></tr><tr><td style ='padding-left:50px;padding-right:50px'>"
                //<b>"
                + "<br />親愛的同仁您好：<br />提醒您！您報名之　<font color='Blue'>" + dr["activity_name"].ToString() + "</font>　活動<br/> 因有人臨時取消報名，你已由原備取轉為正取<br/>"
                + "<font color='Blue'>目前編號：</font>正取" + dr["SeqNo"].ToString()
                + "</td></tr>"
                + "<tr><td align='center' ></font> <a href='" + webPath + "Default.aspx" + "'>" + "報名系統聯結" + " </a></td> </tr>"
                + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";

          
          
            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                //clsMyObj.ShowMessage(ex.Message );
                WriteErrorLog("BatchUpdate", ex.Message, "SendCanJoinMail", "Sys", "0");


            }



        }


    }
    /// <summary>
    /// 取得N天後活動開始的人員清冊
    /// </summary>
    /// <param name="BeforeType">3代表是3天前</param>
    /// <returns></returns>
    private DataTable GetRegistedActivityList(string BeforeType)
    {


        StringBuilder sb = new StringBuilder();
        sb.AppendLine(" create table  #DD");
        sb.AppendLine(" (");
        sb.AppendLine(" id varchar(50),");
        sb.AppendLine(" emp_id nvarchar(100),");
        sb.AppendLine(" SeqNo int IDENTITY(1,1) NOT NULL");
        sb.AppendLine(" ,limit_count int ");
        sb.AppendLine(" )");
        sb.AppendLine(" create table  #EE");
        sb.AppendLine(" (");
        sb.AppendLine(" id varchar(50),");
        sb.AppendLine(" emp_id nvarchar(100),");
        sb.AppendLine(" SeqNo int,");
        sb.AppendLine(" limit_count int ");
        sb.AppendLine(" )");
        sb.AppendLine(" declare AA_Cur cursor for ");
        sb.AppendLine(" select convert(varchar(50),id) ,limit_count from Activity");
        sb.AppendLine(" declare @id varchar(50),@limit_count int");
        sb.AppendLine(" OPEN AA_Cur");
        sb.AppendLine(" FETCH NEXT FROM AA_Cur into @id ,@limit_count");
        sb.AppendLine(" WHILE @@FETCH_STATUS = 0");
        sb.AppendLine(" BEGIN");

        sb.AppendLine(" insert into #DD ");
        sb.AppendLine(" select convert(varchar(50),activity_id ),emp_id ,@limit_count ");
        sb.AppendLine(" from  ActivityRegist ");
        sb.AppendLine(" where convert(varchar(50),activity_id) =@id  and check_status >=0");

        sb.AppendLine(" order by createat ");

        sb.AppendLine(" insert into #EE ");
        sb.AppendLine(" select * from #DD --  where SeqNo<=limit_count");
        sb.AppendLine(" truncate table #DD");

        sb.AppendLine(" FETCH NEXT FROM AA_Cur into @id ,@limit_count");
        sb.AppendLine(" end");

        if (BeforeType == "3")
        {
            sb.AppendLine("  SELECT COMPANY_CODE ");
            sb.AppendLine("    INTO #AA");
            sb.AppendLine("     FROM ACSM_CALENDAR ");
            sb.AppendLine("      WHERE [DAY]= CONVERT(datetime,convert(varchar(10),getdate(),111))");
            sb.AppendLine("      AND NOT ISNULL(HOLIDAY_TYPE,'') IN('S','H')");


            sb.AppendLine("    create table #BB( [DAY] DATETIME, COMPANY_CODE VARCHAR(100) )");
            sb.AppendLine("     DECLARE @COMPANY_CODE VARCHAR(100)");
            sb.AppendLine("      declare calendar_cur CURSOR ");
            sb.AppendLine("       FOR  SELECT * FROM #AA");
            sb.AppendLine("      OPEN calendar_cur");
            sb.AppendLine("      FETCH NEXT FROM calendar_cur INTO @COMPANY_CODE;");
            sb.AppendLine("       WHILE @@FETCH_STATUS = 0");
            sb.AppendLine("       BEGIN ");
            sb.AppendLine("     INSERT INTO #BB");
            sb.AppendLine("         SELECT TOP 3 [DAY],COMPANY_CODE   ");
            sb.AppendLine("          FROM ACSM_CALENDAR  WHERE COMPANY_CODE =@COMPANY_CODE AND [DAY]>GETDATE()");
            sb.AppendLine("     AND NOT ISNULL( HOLIDAY_TYPE,'') IN('S','H')");
            sb.AppendLine("         ORDER BY [DAY]  ");
            sb.AppendLine("         FETCH NEXT FROM calendar_cur INTO @COMPANY_CODE");
            sb.AppendLine("     END;");
            sb.AppendLine("    CLOSE calendar_cur;");
            sb.AppendLine("     DEALLOCATE calendar_cur;");
            sb.AppendLine("       select COMPANY_CODE ,MAX([DAY] ) as MAXDay");
            sb.AppendLine("      INTO #CC");
            sb.AppendLine("    from #BB ");
            sb.AppendLine("  GROUP BY COMPANY_CODE");


            sb.AppendLine("   create table #BB2( [DAY] DATETIME, COMPANY_CODE VARCHAR(100) )");

            sb.AppendLine("   declare calendar_cur2 CURSOR ");
            sb.AppendLine("     FOR  SELECT * FROM #AA");
            sb.AppendLine("     OPEN calendar_cur2");
            sb.AppendLine("    FETCH NEXT FROM calendar_cur2 INTO @COMPANY_CODE;");
            sb.AppendLine("    WHILE @@FETCH_STATUS = 0");
            sb.AppendLine("     BEGIN ");
            sb.AppendLine("     INSERT INTO #BB2");
            sb.AppendLine("     SELECT TOP 2 [DAY],COMPANY_CODE ");
            sb.AppendLine("     FROM ACSM_CALENDAR  WHERE COMPANY_CODE =@COMPANY_CODE AND [DAY]>GETDATE()");
            sb.AppendLine("         AND NOT ISNULL( HOLIDAY_TYPE,'') IN('S','H')");
            sb.AppendLine("          ORDER BY [DAY]  ");
            sb.AppendLine("         FETCH NEXT FROM calendar_cur2 INTO @COMPANY_CODE");
            sb.AppendLine("      END;");
            sb.AppendLine("     CLOSE calendar_cur2;");
            sb.AppendLine("     DEALLOCATE calendar_cur2;");
            sb.AppendLine("     select COMPANY_CODE ,MAX([DAY] ) as MAXDay");
            sb.AppendLine("     INTO #CC2");
            sb.AppendLine("    from #BB2");
            sb.AppendLine("     GROUP BY COMPANY_CODE");




            sb.AppendLine("      delete from #CC where MAXDay <dateadd(day,3,convert(date,GETDATE()))");
            sb.AppendLine("     select E.SeqNo,E.limit_count,A.activity_type,A.activity_name ,B.emp_id ,convert(varchar(20),A.activity_startdate,120) as activity_startdate ");
            sb.AppendLine("     ,convert(varchar(20),A.activity_enddate,120) as activity_enddate");
            sb.AppendLine("      ,C.WORK_ID ,C.NATIVE_NAME,C.OFFICE_MAIL ,C.COMPANY_CODE");
            sb.AppendLine("     from  Activity  A");
            sb.AppendLine("       left join ActivityRegist B on A.id=B.activity_id");
            sb.AppendLine("    left join V_ACSM_USER2 C on C.ID=B.emp_id ");
            sb.AppendLine("    LEFT JOIN #CC D ON C.COMPANY_CODE=D.COMPANY_CODE ");
            sb.AppendLine("        LEFT JOIN #CC2 D2 ON C.COMPANY_CODE=D2.COMPANY_CODE ");
            sb.AppendLine("       inner join #EE E on convert(varchar(50),B.activity_id)=E.id and B.emp_id=E.emp_id");
            sb.AppendLine("      WHERE A.activity_type ='1' and A.Send3DayMail=1 and not B.activity_id is null and B.check_status =0");
            sb.AppendLine("      AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))<=D.MAXDay ");
            sb.AppendLine("     AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))>D2.MAXDay   ");


            sb.AppendLine("      union    select E.SeqNo,E.limit_count, A.activity_type,A.activity_name ,B.emp_id ,convert(varchar(20),A.activity_startdate,120) as activity_startdate ");
            sb.AppendLine("     ,convert(varchar(20),A.activity_enddate,120) as activity_enddate ");
            sb.AppendLine("      ,C.WORK_ID ,C.NATIVE_NAME,C.OFFICE_MAIL ,C.COMPANY_CODE");
            sb.AppendLine("      from  Activity  A");
            sb.AppendLine("     left join ActivityTeamMember B on A.id=B.activity_id ");
            sb.AppendLine("      left join V_ACSM_USER2 C on C.ID=B.emp_id ");
            sb.AppendLine("     LEFT JOIN #CC D ON C.COMPANY_CODE=D.COMPANY_CODE ");
            sb.AppendLine("       LEFT JOIN #CC2 D2 ON C.COMPANY_CODE=D2.COMPANY_CODE ");
            sb.AppendLine("       inner join #EE E on convert(varchar(50),B.activity_id)=E.id and B.boss_id=E.emp_id");
            sb.AppendLine("      where  A.activity_type ='2'  and A.Send3DayMail=1  and not B.activity_id is null and B.check_status =0");
            sb.AppendLine("      AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))<=D.MAXDay  ");
            sb.AppendLine("      AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))>D2.MAXDay");
            sb.AppendLine("      DROP TABLE #AA DROP TABLE #BB DROP TABLE #CC drop TABLE #BB2 DROP TABLE #CC2");

        }
        else
        {
            sb.AppendLine(" SELECT COMPANY_CODE ");
            sb.AppendLine(" INTO #AA");
            sb.AppendLine(" FROM ACSM_CALENDAR  ");
            sb.AppendLine(" WHERE [DAY]= CONVERT(datetime,convert(varchar(10),getdate(),111))");
            sb.AppendLine(" AND NOT ISNULL(HOLIDAY_TYPE,'') IN('S','H')");
            sb.AppendLine(" create table #BB( [DAY] DATETIME, COMPANY_CODE VARCHAR(100) )");
            sb.AppendLine(" DECLARE @COMPANY_CODE VARCHAR(100)");
            sb.AppendLine(" declare calendar_cur CURSOR ");
            sb.AppendLine(" FOR  SELECT * FROM #AA");
            sb.AppendLine(" OPEN calendar_cur");
            sb.AppendLine(" FETCH NEXT FROM calendar_cur INTO @COMPANY_CODE;");
            sb.AppendLine(" WHILE @@FETCH_STATUS = 0");
            sb.AppendLine(" BEGIN ");
            sb.AppendLine(" INSERT INTO #BB");
            sb.AppendLine(" SELECT TOP 1 [DAY],COMPANY_CODE      ");
            sb.AppendLine(" FROM ACSM_CALENDAR  WHERE COMPANY_CODE =@COMPANY_CODE AND [DAY]>GETDATE()");
            sb.AppendLine("     AND NOT ISNULL( HOLIDAY_TYPE,'') IN('S','H')");
            sb.AppendLine("      ORDER BY [DAY]  ");
            sb.AppendLine("     FETCH NEXT FROM calendar_cur INTO @COMPANY_CODE");
            sb.AppendLine(" END;");
            sb.AppendLine(" CLOSE calendar_cur;");
            sb.AppendLine(" DEALLOCATE calendar_cur;");
            sb.AppendLine(" select COMPANY_CODE ,MAX([DAY] ) as MAXDay");
            sb.AppendLine(" INTO #CC");
            sb.AppendLine(" from #BB ");
            sb.AppendLine(" GROUP BY COMPANY_CODE");
            sb.AppendLine(" select E.SeqNo,E.limit_count,A.activity_type, A.activity_name ,B.emp_id ,convert(varchar(20),A.activity_startdate,120) as activity_startdate ");
            sb.AppendLine(" ,convert(varchar(20),A.activity_enddate,120) as activity_enddate ");
            sb.AppendLine(" ,C.WORK_ID ,C.NATIVE_NAME,C.OFFICE_MAIL ,C.COMPANY_CODE");
            sb.AppendLine(" from  Activity  A");
            sb.AppendLine(" left join ActivityRegist B on A.id=B.activity_id");
            sb.AppendLine(" left join V_ACSM_USER2 C on C.ID=B.emp_id ");
            sb.AppendLine(" LEFT JOIN #CC D ON C.COMPANY_CODE=D.COMPANY_CODE ");
            sb.AppendLine("  inner join #EE E on convert(varchar(50),B.activity_id)=E.id and B.emp_id=E.emp_id");
            sb.AppendLine(" WHERE A.activity_type ='1'  and A.Send1DayMail=1 and not B.activity_id is null and B.check_status =0");
            sb.AppendLine(" AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))<=D.MAXDay ");
            sb.AppendLine(" AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))>getdate()  ");
            sb.AppendLine(" union    select E.SeqNo,E.limit_count,A.activity_type,A.activity_name ,B.emp_id ,convert(varchar(20),A.activity_startdate,120) as activity_startdate ");
            sb.AppendLine(" ,convert(varchar(20),A.activity_enddate,120) as activity_enddate ");
            sb.AppendLine(" ,C.WORK_ID ,C.NATIVE_NAME,C.OFFICE_MAIL ,C.COMPANY_CODE");
            sb.AppendLine(" from  Activity  A");
            sb.AppendLine(" left join ActivityTeamMember B on A.id=B.activity_id ");
            sb.AppendLine(" left join V_ACSM_USER2 C on C.ID=B.emp_id ");
            sb.AppendLine(" LEFT JOIN #CC D ON C.COMPANY_CODE=D.COMPANY_CODE ");
            sb.AppendLine("  inner join #EE E on convert(varchar(50),B.activity_id)=E.id and B.boss_id=E.emp_id");
            sb.AppendLine(" where  A.activity_type ='2'  and A.Send1DayMail=1 and not B.activity_id is null and B.check_status =0");
            sb.AppendLine(" AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))<=D.MAXDay  ");
            sb.AppendLine(" AND convert(datetime,convert(varchar(10),A.activity_startdate ,111))>getdate()  ");

            sb.AppendLine(" DROP TABLE #AA DROP TABLE #BB DROP TABLE #CC");

        }


        sb.AppendLine("  drop table #DD");
        sb.AppendLine("drop table #EE");
        sb.AppendLine("CLOSE AA_Cur;");
        sb.AppendLine("DEALLOCATE AA_Cur;");
        return SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["destination_connectionstring"].Trim(), CommandType.Text, sb.ToString()).Tables[0];
    }
    public bool ChkHoliday(string COMPANY_CODE, DateTime dd)
    {

        StringBuilder sb = new StringBuilder();
        sb.AppendLine("select HOLIDAY_TYPE  from ACSM_CALENDAR  where COMPANY_CODE =@COMPANY_CODE and [DAY]=@dd ");

        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@COMPANY_CODE", SqlDbType.NVarChar, COMPANY_CODE.Length);
        sqlParams[0].Value = COMPANY_CODE;
        sqlParams[1] = new SqlParameter("@dd", SqlDbType.DateTime);
        sqlParams[1].Value = dd;


        try
        {

            string holiday = SqlHelper.ExecuteScalar(conn, CommandType.Text, sb.ToString(), sqlParams).ToString();
            if (holiday.ToUpper() == "H" || holiday.ToUpper() == "S")
            {
                return true;
            }
            else
            {

                return false;
            }
        }
        catch
        {

            return true;
        }
    
    
    
    }

    /// <summary>
    /// 寄送1天後活動開始信件
    /// </summary>
    public void SendRegistedActivityMailBefor1Day()
    {
        MailMessage mail;
        DataTable dt = GetRegistedActivityList("1");
        string mailtype = System.Configuration.ConfigurationManager.AppSettings["MailType"].ToLower();
        string[] smtpto = System.Configuration.ConfigurationManager.AppSettings["SMTPTo"].Split(',');
         string webPath = System.Configuration.ConfigurationManager.AppSettings["webPath"].ToLower();
        MailAddress mailFrom = new MailAddress(System.Configuration.ConfigurationManager.AppSettings["SMTPFrom"], "報名系統通知");
        SmtpClient smtp = new SmtpClient(System.Configuration.ConfigurationManager.AppSettings["SMTPServer"]);
        Int64  limit, seq;
        foreach (DataRow dr in dt.Rows)
        {
            if (ChkHoliday(dr["COMPANY_CODE"].ToString (),DateTime.Today ))//今天是休假日就不發信
            {
                continue;

            }

            mail = new MailMessage();
            if ((string.Compare(mailtype, "online") != 0))
            {
                foreach (string st1 in smtpto)
                {
                    mail.To.Add(st1);
                }
            }
            else
            {
                mail.To.Add(dr["OFFICE_MAIL"].ToString());
            }

            mail.Subject = "活動名稱：" + dr["activity_name"] + "，活動開始前1天提醒";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;
            mail.Body = "<table border='1'  ><tr><td style='background:#548DD4;Color:White' align='center' > 活動前1日提醒通知</td></tr><tr><td style ='padding-left:50px;padding-right:50px'>"
                //<b>"
                + "<br />親愛的同仁您好：<br />提醒您！您報名之　<font color='Blue'>" + dr["activity_name"].ToString() + "</font>　活動，即將於下個工作天開始進行，<br />敬請您準時參加，謝謝您～  <br /><b>-------------------------------------------------------</b><br /> "
                + "<b><font color='Blue'>.姓名：</font></b>" + dr["NATIVE_NAME"].ToString() + "<br />"
                + "<b><font color='Blue'>.工號：</font></b>" + dr["WORK_ID"].ToString() + "<br />"
                + "<b><font color='Blue'>.活動開始時間：</font></b>" + DateTime.Parse(dr["activity_startdate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />"
                  + "<b><font color='Blue'>.活動結束時間：</font></b>" + DateTime.Parse(dr["activity_enddate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />";
            seq = Int64.Parse(dr["SeqNo"].ToString());
            limit = Int64.Parse(dr["limit_count"].ToString());
            if (seq <= limit)
            {
                mail.Body += "<b><font color='Blue'>.報名序號：</font></b>正取" + dr["SeqNo"].ToString() + "(註:序號若與報名成功通知序號不一致，為期間有人員取消報名，系統重新排序)<br /><br />"

                    + "</td></tr>"
                    + "<tr><td align='center' ></font> <a href='" + webPath + "Default.aspx" + "'>" + "報名系統聯結" + " </a></td> </tr>"

                    + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";
            }
            else
            {
                mail.Body += "<b><font color='Blue'>.報名序號：</font></b>備取" + (seq - limit).ToString() + "(註:序號若與報名成功通知序號不一致，為期間有人員取消報名，系統重新排序)<br /><br />"

                   + "</td></tr>"
                   + "<tr><td align='center' ></font> <a href='" + webPath + "Default.aspx" + "'>"  + "報名系統聯結" + " </a></td> </tr>"

                   + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";

            }

            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                //clsMyObj.ShowMessage(ex.Message );
                WriteErrorLog("BatchUpdate", ex.Message, "SendOneDayMail", "Sys", "0");


            }



        }
    }

    /// <summary>
    /// 寄送3天後活動開始信件
    /// </summary>
    public void SendRegistedActivityMailBefor3Days()
    {
        MailMessage mail;
        DataTable dt = GetRegistedActivityList("3");
        string mailtype = System.Configuration.ConfigurationManager.AppSettings["MailType"].ToLower();
        string[] smtpto = System.Configuration.ConfigurationManager.AppSettings["SMTPTo"].Split(',');
        MailAddress mailFrom = new MailAddress(System.Configuration.ConfigurationManager.AppSettings["SMTPFrom"], "報名系統通知");
        string webPath = System.Configuration.ConfigurationManager.AppSettings["webPath"].ToLower();
        SmtpClient smtp = new SmtpClient(System.Configuration.ConfigurationManager.AppSettings["SMTPServer"]);
        Int64 limit, seq;
        foreach (DataRow dr in dt.Rows)
        {
            if (ChkHoliday(dr["COMPANY_CODE"].ToString(), DateTime.Today))//今天是休假日就不發信
            {
                continue;

            }

            mail = new MailMessage();
            if ((string.Compare(mailtype, "online") != 0))
            {
                foreach (string st1 in smtpto)
                {
                    mail.To.Add(st1);
                }
            }
            else
            {
                mail.To.Add(dr["OFFICE_MAIL"].ToString());
            }

            mail.Subject = "活動名稱：" + dr["activity_name"] + "，活動開始前3天提醒.";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;
            seq = Int64.Parse(dr["SeqNo"].ToString());
            limit = Int64.Parse(dr["limit_count"].ToString());
            mail.Body = "<table border='1' ><tr><td style='background:#548DD4;Color:White' align='center' > 活動前3日提醒通知</td></tr><tr><td style ='padding-left:50px;padding-right:50px'>"
                //<b>"
               + "<br />親愛的同仁您好：<br />提醒您！您報名之　<font color='Blue'>" + dr["activity_name"].ToString() + "</font>　活動，即將於3個工作天後開始進行，敬請您準時參加，謝謝您～  <br /><b>-------------------------------------------------------</b><br /> "
               + "<b><font color='Blue'>.姓名：</font></b>" + dr["NATIVE_NAME"].ToString() + "<br />"
               + "<b><font color='Blue'>.工號：</font></b>" + dr["WORK_ID"].ToString() + "<br />"
               + "<b><font color='Blue'>.活動開始時間：</font></b>" + DateTime.Parse(dr["activity_startdate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />"
               + "<b><font color='Blue'>.活動結束時間：</font></b>" + DateTime.Parse(dr["activity_enddate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />";

            if (seq <= limit)
            {
                mail.Body += "<b><font color='Blue'>.報名序號：</font></b>正取" + dr["SeqNo"].ToString() + "(註:序號若與報名成功通知序號不一致，為期間有人員取消報名，系統重新排序)<br /><br />"

               + "</td></tr>"
               + "<tr><td align='center' ></font> <a href='" + webPath +   "Default.aspx'>" + "報名系統聯結" + " </a></td> </tr>"

               + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";
            }
            else
            {
                mail.Body += "<b><font color='Blue'>.報名序號：</font></b>備取" + (seq - limit).ToString() + "(註:序號若與報名成功通知序號不一致，為期間有人員取消報名，系統重新排序)<br /><br />"

                  + "</td></tr>"
                  + "<tr><td align='center' ></font> <a href='" + webPath + "Default.aspx'>" + "報名系統聯結" + " </a></td> </tr>"

                  + "<tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";
            }
            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                //clsMyObj.ShowMessage(ex.Message );
                WriteErrorLog("BatchUpdate", ex.Message, "Send3DaysMail", "Sys", "0");
            }



        }
    }

    /// <summary>
    /// 取得1天後開始但尚未報名的人員清冊(需在限制人員清冊中才會出現)
    /// </summary>
    /// <returns></returns>
    private DataTable GetUnRegistedActivityList()
    {

        StringBuilder sb = new StringBuilder();
        sb.AppendLine(" SELECT COMPANY_CODE ");
        sb.AppendLine(" INTO #AAA ");
        sb.AppendLine(" FROM ACSM_CALENDAR  ");
        sb.AppendLine(" WHERE [DAY]= CONVERT(datetime,convert(varchar(10),getdate(),111))");
        sb.AppendLine(" AND NOT ISNULL(HOLIDAY_TYPE,'') IN('S','H')");
        sb.AppendLine(" create table #BBB( [DAY] DATETIME,COMPANY_CODE VARCHAR(100))");
        sb.AppendLine(" DECLARE @COMPANY_CODE VARCHAR(100)");
        sb.AppendLine(" declare calendar_cur CURSOR ");
        sb.AppendLine(" FOR  SELECT * FROM #AAA");
        sb.AppendLine(" OPEN calendar_cur");
        sb.AppendLine(" FETCH NEXT FROM calendar_cur INTO @COMPANY_CODE;");
        sb.AppendLine(" WHILE @@FETCH_STATUS = 0");
        sb.AppendLine(" BEGIN");
        sb.AppendLine(" INSERT INTO #BBB");
        sb.AppendLine("     SELECT TOP 1 [DAY],COMPANY_CODE  ");
        sb.AppendLine(" FROM ACSM_CALENDAR  WHERE COMPANY_CODE =@COMPANY_CODE AND [DAY]>GETDATE()");
        sb.AppendLine("     AND NOT ISNULL( HOLIDAY_TYPE,'') IN('S','H')");
        sb.AppendLine("      ORDER BY [DAY]  ");
        sb.AppendLine("     FETCH NEXT FROM calendar_cur INTO @COMPANY_CODE");
        sb.AppendLine(" END;");
        sb.AppendLine(" CLOSE calendar_cur;");
        sb.AppendLine(" DEALLOCATE calendar_cur;");
        sb.AppendLine(" select COMPANY_CODE ,MAX([DAY] ) as MAXDay ");
        sb.AppendLine(" INTO #CCC");
        sb.AppendLine(" from #BBB");
        sb.AppendLine(" GROUP BY COMPANY_CODE");
        sb.AppendLine(" select A.id,COUNT(C.emp_id ) As RegistedCounter");
        sb.AppendLine(" into #AA");
        sb.AppendLine(" from Activity A");
        sb.AppendLine(" left join ActivityRegist C on C.activity_id =A.id ");
        sb.AppendLine(" LEFT JOIN V_ACSM_USER2  D ON C.emp_id =D.ID");
        sb.AppendLine(" LEFT JOIN #CCC E ON D.COMPANY_CODE =E.COMPANY_CODE   ");
        sb.AppendLine(" where A.is_grouplimit ='Y'and ");
        sb.AppendLine(" A.regist_deadline=E.MAXDay and A.SendUnregist=1 and A.active='Y' ");
        sb.AppendLine(" group by A.id ");
        sb.AppendLine(" select *  into #BB from (");
        sb.AppendLine(" Select A.id,A.limit_count,E.RegistedCounter  ,A.activity_type,NATIVE_NAME ,");
        sb.AppendLine(" convert(varchar(10),A.regist_deadline,120) as regist_deadline,D.WORK_ID ,D.OFFICE_MAIL ,A.activity_name ,convert(varchar(20),A.activity_startdate,120) as activity_startdate  ,convert(varchar(20),A.activity_enddate,120) as activity_enddate ");
        sb.AppendLine(" ,D.COMPANY_CODE");
            
        sb.AppendLine(" from Activity  A");
        sb.AppendLine(" left join (select distinct activity_id ,emp_id from ActivityGroupLimit union select distinct A.activity_id ,B.ID as emp_id  from ActivityLimitCompany A inner join V_ACSM_USER2 B on A.COMPANY_CODE=B.COMPANY_CODE) B on A.id=B.activity_id ");
        sb.AppendLine(" left join V_ACSM_USER2  D on B.emp_id =D.ID");
        sb.AppendLine(" left join ActivityRegist C on C.activity_id =A.id and C.emp_id=B.emp_id ");
        sb.AppendLine(" left join #AA  E on A.id=E.id ");
        sb.AppendLine(" LEFT JOIN #CCC F ON D.COMPANY_CODE=F.COMPANY_CODE ");
        sb.AppendLine(" where A.is_grouplimit ='Y'and a.activity_type='1'and A.SendUnregist=1  and A.active='Y' and ");
        sb.AppendLine(" F.MAXDay >=A.regist_deadline ");
        sb.AppendLine(" and A.regist_deadline >GetDate()");
        sb.AppendLine(" and C.emp_id is null --未報名");
        sb.AppendLine(" and not B.emp_id is null ---族群內的名單");
        sb.AppendLine(" and isnull(E.RegistedCounter,0 )<A.limit_count --未達報名人數");
        sb.AppendLine(" union Select  A.id,A.limit_count,E.RegistedCounter  ,A.activity_type,NATIVE_NAME , convert(varchar(10),A.regist_deadline,120) as regist_deadline,D.WORK_ID ,D.OFFICE_MAIL ,A.activity_name ,convert(varchar(20),A.activity_startdate,120) as activity_startdate  ,convert(varchar(20),A.activity_enddate,120) as activity_enddate ");
        sb.AppendLine(" ,D.COMPANY_CODE");
        sb.AppendLine(" from Activity  A");
        sb.AppendLine(" left join (select distinct activity_id ,emp_id from ActivityGroupLimit union select distinct A.activity_id ,B.ID as emp_id from ActivityLimitCompany A inner join V_ACSM_USER2 B on A.COMPANY_CODE=B.COMPANY_CODE) B on A.id=B.activity_id");
        sb.AppendLine(" left join V_ACSM_USER2  D on B.emp_id =D.ID");
        sb.AppendLine(" left join ActivityTeamMember C on C.activity_id =A.id and C.emp_id=B.emp_id ");
        sb.AppendLine(" left join #AA  E on A.id=E.id ");
        sb.AppendLine(" LEFT JOIN #CCC F ON D.COMPANY_CODE =F.COMPANY_CODE ");
        sb.AppendLine(" where A.is_grouplimit ='Y'and a.activity_type='2'and A.SendUnregist=1 and ");
        sb.AppendLine(" F.MAXDay >=A.regist_deadline ");
        sb.AppendLine(" and A.regist_deadline >GetDate()");
        sb.AppendLine(" and C.emp_id is null ---未報名的");
        sb.AppendLine(" and not B.emp_id is null ---族群內的名單");
        sb.AppendLine(" and isnull(E.RegistedCounter,0 )<A.limit_count --未達報名人數");
        sb.AppendLine(" ) tb  select A.activity_info ,B.* from #BB B left join Activity A on A.id =B.id ");
        sb.AppendLine(" drop Table #AA drop Table #BB  drop Table #AAA drop Table #BBB drop Table #CCC ");

        return SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["destination_connectionstring"].Trim(), CommandType.Text, sb.ToString()).Tables[0];


    }


    /// <summary>
    /// 取得活動的附件(補充說明文件)
    /// </summary>
    /// <param name="dirName"></param>
    /// <returns></returns>
    public string getInfo(string dirName)
    {
        DirectoryInfo myDirectoryInfo = new DirectoryInfo(dirName);

        string FileNames = "";

        if (myDirectoryInfo.Exists)
        {

            foreach (FileInfo myFileInfo in myDirectoryInfo.GetFiles())
            {

                FileNames += myFileInfo.FullName + ";";
            }
        }
        if (FileNames.EndsWith(";"))
        {
            FileNames.Substring(0, FileNames.Length - 2);
        }

        return FileNames;
    }

    /// <summary>
    /// 取得人事系統中的行事曆設定
    /// </summary>
    /// <returns></returns>
    public DataTable GetCalendar()
    {
        try
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * ");
            sb.AppendLine("FROM V_ACSM_CALENDAR  ");

            DataSet DS = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["source_connectionstring"].Trim(), CommandType.Text, sb.ToString(), null);

            if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
            {
                return DS.Tables[0];
            }
            else
            {
                LogMsg.Log("[SelectEmployeeData]-Rows Count = 0 ", 1, false);
                return null;
            }
        }
        catch (Exception ex)
        {
            LogMsg.Log(string.Format("[SelectEmployeeData]-Exception:{0}", ex.Message), 1, false);
            WriteErrorLog("BatchUpdate", ex.Message, "GetCalendar", "Sys", "0");
            return null;
        }

    }


    /// <summary>
    /// 更新系統的行事曆資料
    /// </summary>
    /// <param name="dt"></param>
    public void UpdateCalendar(DataTable dt)
    {
        string dd = "";
        StringBuilder sb = new StringBuilder();
        sb.AppendLine("truncate Table ACSM_CALENDAR_TMP; ");

        SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["destination_connectionstring"].Trim(), CommandType.Text, sb.ToString());


        foreach (DataRow dr in dt.Rows)
        {
            dd = DateTime.Parse(dr["DAY"].ToString()).ToString("yyyy/MM/dd");
            sb.Length = 0;
            sb.AppendLine("insert into ACSM_CALENDAR_TMP ([DAY],[COMPANY_CODE],[HOLIDAY_TYPE]) VALUES( ");
            sb.AppendLine(string.Format("'{0}','{1}','{2}')  ", dd, dr["COMPANY_CODE"].ToString(), dr["HOLIDAY_TYPE"].ToString()));
            SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["destination_connectionstring"].Trim(), CommandType.Text, sb.ToString());
        }
        sb.Length = 0;
        sb.AppendLine("EXEC sp_rename 'ACSM_CALENDAR','ACSM_CALENDAR_TMP2'; ");
        sb.AppendLine("EXEC sp_rename 'ACSM_CALENDAR_TMP','ACSM_CALENDAR'; ");
        sb.AppendLine("EXEC sp_rename 'ACSM_CALENDAR_TMP2','ACSM_CALENDAR_TMP'; ");

        SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["destination_connectionstring"].Trim(), CommandType.Text, sb.ToString(), null);

    }



    /// <summary>
    /// 寄送未報名信件
    /// </summary>
    public void SendUnRegistedActivityMail()
    {
        MailMessage mail;
        DataTable dt = GetUnRegistedActivityList();
        string mailtype = System.Configuration.ConfigurationManager.AppSettings["MailType"].ToLower();
        string webPath = System.Configuration.ConfigurationManager.AppSettings["webPath"].ToLower();
        string webDisk = System.Configuration.ConfigurationManager.AppSettings["webDisk"].ToLower();
        string[] smtpto = System.Configuration.ConfigurationManager.AppSettings["SMTPTo"].Split(',');
        MailAddress mailFrom = new MailAddress(System.Configuration.ConfigurationManager.AppSettings["SMTPFrom"], "報名系統通知");
        SmtpClient smtp = new SmtpClient(System.Configuration.ConfigurationManager.AppSettings["SMTPServer"]);
        string[] FileNames;
        foreach (DataRow dr in dt.Rows)
        {
            if (ChkHoliday(dr["COMPANY_CODE"].ToString(),DateTime .Today ))
            {
                continue;
            
            }



            mail = new MailMessage();
            if ((string.Compare(mailtype, "online") != 0))
            {
                foreach (string st1 in smtpto)
                {
                    mail.To.Add(st1);
                }
            }
            else
            {
                mail.To.Add(dr["OFFICE_MAIL"].ToString());
            }

            mail.Subject = "活動名稱：" + dr["activity_name"] + "，活動未報名提醒.";
            //寄件者
            mail.From = mailFrom;
            mail.IsBodyHtml = true;

            //取得附加檔案
            FileNames = getInfo(webDisk + dr["id"].ToString().Trim()).Split(';');
            Attachment data;

            foreach (string FileName in FileNames)
            {
                if (FileName != null && FileName != "")
                {
                    data = new Attachment(FileName);
                    mail.Attachments.Add(data);
                }
            }



            if (dr["activity_type"].ToString().Trim() == "1")
            {

                mail.Body = "<table border='1' ><tr><td style='background:#548DD4;Color:White' align='center'  ><b><font Color='White'>報名提醒通知</font></b></td></tr><tr> "

                    + "<tr><td style ='padding-left:50px;padding-right:50px'><br />親愛的同仁，您好～<br /> 您為　<a href='" + webPath + "Default.aspx'>" + dr["activity_name"].ToString() + "</a>" + "　活動主要服務族群，<br /> 提醒您！您可參考此活動內容，連結上方報名系統網址，請於下個工作日前完成報名，<br />謝謝您～<br />---------------------------------------------------------------------------------------------- <br />"
                  + "<b><font Color='Blue'>  .活動名稱：</font></b>" + dr["activity_name"].ToString() + "<br />"
                   // + "<tr><td><a href='" + webPath + "WebForm/RegistActivity/RegistActivity_Person.aspx'>報名網址連結</a>"

   + "<b><font color='Blue'>    .活動開始時間：</font></b>" + DateTime.Parse(dr["activity_startdate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />"
                 + "<b><font color='Blue'>    .活動結束時間：</font></b>" + DateTime.Parse(dr["activity_enddate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />"
               + "<b><font color='Blue'>    .截止報名日期：</font></b>" + DateTime.Parse(dr["regist_deadline"].ToString()).ToString("yyyy/MM/dd") + "<br /><br />"
               
                 + "</td></tr><tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";

            }
            else
            {
                mail.Body = "<table border='1' ><tr><td style='background:#548DD4;Color:White' align='center'  ><b><font Color='White'>報名提醒通知</font></b></td></tr> "

                   + "<tr><td style ='padding-left:50px;padding-right:50px'><br /> 親愛的同仁，您好～<br /> 您為　<a href='" + webPath + "Default.aspx'>" + dr["activity_name"].ToString() + "</a>" + "　活動主要服務族群，<br /> 提醒您！您可參考此活動內容，連結上方報名系統網址，請於下個工作日前完成報名，<br />謝謝您～<br />---------------------------------------------------------------------------------------------- <br />"
                  + "<b><font Color='Blue'>  .活動名稱：</font></b>" + dr["activity_name"].ToString() + "<br />"
                    // + "<tr><td><a href='" + webPath + "WebForm/RegistActivity/RegistActivity_Person.aspx'>報名網址連結</a>"

  + "<b><font color='Blue'>    .活動開始時間：</font></b>" + DateTime.Parse(dr["activity_startdate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />"
                + "<b><font color='Blue'>    .活動結束時間：</font></b>" + DateTime.Parse(dr["activity_enddate"].ToString()).ToString("yyyy/MM/dd HH:mm") + "<br />"
              + "<b><font color='Blue'>    .截止報名日期：</font></b>" + DateTime.Parse(dr["regist_deadline"].ToString()).ToString("yyyy/MM/dd") + "<br /><br />"

                + "</td></tr><tr><td style='background:#548DD4' align='center'  >  &nbsp;  </td> </tr></table>";
            }
            try
            {
                smtp.Send(mail);

            }
            catch (Exception ex)
            {
                WriteErrorLog("BatchUpdate", ex.Message, "SendUnReisterMail", "Sys", "0");

            }



        }
    }
}